package generator

import (
	"database/sql"
	"fmt"
	"strconv"
	"strings"

	"pgm/internal/database"
	"pgm/internal/introspect"
	"pgm/internal/models"
)

// DynamicAPI 动态API生成器
type DynamicAPI struct {
	inspector *introspect.Inspector
	db        *sql.DB
}

// NewDynamicAPI 创建动态API生成器
func NewDynamicAPI() *DynamicAPI {
	return &DynamicAPI{
		inspector: introspect.NewInspector(),
		db:        database.DB,
	}
}

// GetRecords 获取记录列表
func (api *DynamicAPI) GetRecords(tableName string, pagination models.PaginationRequest) (*models.PaginationResponse, error) {
	// 获取表信息
	table, err := api.inspector.GetTableByName(tableName, "public")
	if err != nil {
		return nil, fmt.Errorf("获取表信息失败: %w", err)
	}

	// 设置默认分页参数
	if pagination.Page <= 0 {
		pagination.Page = 1
	}
	if pagination.PageSize <= 0 {
		pagination.PageSize = 20
	}
	if pagination.PageSize > 100 {
		pagination.PageSize = 100
	}

	// 构建查询语句
	baseQuery := fmt.Sprintf("SELECT * FROM %s", tableName)
	countQuery := fmt.Sprintf("SELECT COUNT(*) FROM %s", tableName)

	var whereClause string
	var args []interface{}
	argIndex := 1

	// 添加搜索条件
	if pagination.Search != "" {
		var searchClauses []string
		for _, col := range table.Columns {
			if isTextColumn(col.DataType) {
				searchClauses = append(searchClauses, fmt.Sprintf("%s ILIKE $%d", col.Name, argIndex))
				args = append(args, "%"+pagination.Search+"%")
				argIndex++
			}
		}
		if len(searchClauses) > 0 {
			whereClause = "WHERE " + strings.Join(searchClauses, " OR ")
		}
	}

	// 获取总数
	var total int64
	fullCountQuery := countQuery
	if whereClause != "" {
		fullCountQuery += " " + whereClause
	}

	err = api.db.QueryRow(fullCountQuery, args...).Scan(&total)
	if err != nil {
		return nil, fmt.Errorf("获取总数失败: %w", err)
	}

	// 构建完整查询
	fullQuery := baseQuery
	if whereClause != "" {
		fullQuery += " " + whereClause
	}

	// 添加排序
	if pagination.OrderBy != "" {
		order := "ASC"
		if strings.ToUpper(pagination.Order) == "DESC" {
			order = "DESC"
		}
		fullQuery += fmt.Sprintf(" ORDER BY %s %s", pagination.OrderBy, order)
	} else if len(table.PrimaryKeys) > 0 {
		fullQuery += fmt.Sprintf(" ORDER BY %s ASC", table.PrimaryKeys[0])
	}

	// 添加分页
	offset := (pagination.Page - 1) * pagination.PageSize
	fullQuery += fmt.Sprintf(" LIMIT %d OFFSET %d", pagination.PageSize, offset)

	// 执行查询
	rows, err := api.db.Query(fullQuery, args...)
	if err != nil {
		return nil, fmt.Errorf("查询数据失败: %w", err)
	}
	defer rows.Close()

	// 解析结果
	records, err := api.parseRows(rows, table.Columns)
	if err != nil {
		return nil, fmt.Errorf("解析查询结果失败: %w", err)
	}

	return &models.PaginationResponse{
		Page:     pagination.Page,
		PageSize: pagination.PageSize,
		Total:    total,
		Data:     records,
	}, nil
}

// GetRecord 获取单个记录
func (api *DynamicAPI) GetRecord(tableName string, id string) (models.DynamicRecord, error) {
	table, err := api.inspector.GetTableByName(tableName, "public")
	if err != nil {
		return nil, fmt.Errorf("获取表信息失败: %w", err)
	}

	if len(table.PrimaryKeys) == 0 {
		return nil, fmt.Errorf("表 %s 没有主键", tableName)
	}

	primaryKey := table.PrimaryKeys[0]
	query := fmt.Sprintf("SELECT * FROM %s WHERE %s = $1", tableName, primaryKey)

	rows, err := api.db.Query(query, id)
	if err != nil {
		return nil, fmt.Errorf("查询记录失败: %w", err)
	}
	defer rows.Close()

	records, err := api.parseRows(rows, table.Columns)
	if err != nil {
		return nil, fmt.Errorf("解析查询结果失败: %w", err)
	}

	if len(records) == 0 {
		return nil, fmt.Errorf("记录不存在")
	}

	return records[0].(models.DynamicRecord), nil
}

// CreateRecord 创建记录
func (api *DynamicAPI) CreateRecord(tableName string, data models.DynamicRecord) (models.DynamicRecord, error) {
	table, err := api.inspector.GetTableByName(tableName, "public")
	if err != nil {
		return nil, fmt.Errorf("获取表信息失败: %w", err)
	}

	var columns []string
	var placeholders []string
	var values []interface{}
	argIndex := 1

	for _, col := range table.Columns {
		if value, exists := data[col.Name]; exists && !isAutoIncrement(col) {
			columns = append(columns, col.Name)
			placeholders = append(placeholders, "$"+strconv.Itoa(argIndex))
			values = append(values, value)
			argIndex++
		}
	}

	if len(columns) == 0 {
		return nil, fmt.Errorf("没有可插入的数据")
	}

	query := fmt.Sprintf(
		"INSERT INTO %s (%s) VALUES (%s) RETURNING *",
		tableName,
		strings.Join(columns, ", "),
		strings.Join(placeholders, ", "),
	)

	rows, err := api.db.Query(query, values...)
	if err != nil {
		return nil, fmt.Errorf("插入记录失败: %w", err)
	}
	defer rows.Close()

	records, err := api.parseRows(rows, table.Columns)
	if err != nil {
		return nil, fmt.Errorf("解析插入结果失败: %w", err)
	}

	if len(records) == 0 {
		return nil, fmt.Errorf("插入失败，未返回记录")
	}

	return records[0].(models.DynamicRecord), nil
}

// UpdateRecord 更新记录
func (api *DynamicAPI) UpdateRecord(tableName string, id string, data models.DynamicRecord) (models.DynamicRecord, error) {
	table, err := api.inspector.GetTableByName(tableName, "public")
	if err != nil {
		return nil, fmt.Errorf("获取表信息失败: %w", err)
	}

	if len(table.PrimaryKeys) == 0 {
		return nil, fmt.Errorf("表 %s 没有主键", tableName)
	}

	var setClauses []string
	var values []interface{}
	argIndex := 1

	for _, col := range table.Columns {
		if value, exists := data[col.Name]; exists && !isPrimaryKey(col.Name, table.PrimaryKeys) {
			setClauses = append(setClauses, fmt.Sprintf("%s = $%d", col.Name, argIndex))
			values = append(values, value)
			argIndex++
		}
	}

	if len(setClauses) == 0 {
		return nil, fmt.Errorf("没有可更新的数据")
	}

	primaryKey := table.PrimaryKeys[0]
	values = append(values, id)

	query := fmt.Sprintf(
		"UPDATE %s SET %s WHERE %s = $%d RETURNING *",
		tableName,
		strings.Join(setClauses, ", "),
		primaryKey,
		argIndex,
	)

	rows, err := api.db.Query(query, values...)
	if err != nil {
		return nil, fmt.Errorf("更新记录失败: %w", err)
	}
	defer rows.Close()

	records, err := api.parseRows(rows, table.Columns)
	if err != nil {
		return nil, fmt.Errorf("解析更新结果失败: %w", err)
	}

	if len(records) == 0 {
		return nil, fmt.Errorf("记录不存在或更新失败")
	}

	return records[0].(models.DynamicRecord), nil
}

// DeleteRecord 删除记录
func (api *DynamicAPI) DeleteRecord(tableName string, id string) error {
	table, err := api.inspector.GetTableByName(tableName, "public")
	if err != nil {
		return fmt.Errorf("获取表信息失败: %w", err)
	}

	if len(table.PrimaryKeys) == 0 {
		return fmt.Errorf("表 %s 没有主键", tableName)
	}

	primaryKey := table.PrimaryKeys[0]
	query := fmt.Sprintf("DELETE FROM %s WHERE %s = $1", tableName, primaryKey)

	result, err := api.db.Exec(query, id)
	if err != nil {
		return fmt.Errorf("删除记录失败: %w", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("获取影响行数失败: %w", err)
	}

	if rowsAffected == 0 {
		return fmt.Errorf("记录不存在")
	}

	return nil
}

// parseRows 解析查询结果
func (api *DynamicAPI) parseRows(rows *sql.Rows, columns []introspect.ColumnInfo) ([]interface{}, error) {
	var records []interface{}

	columnNames := make([]string, len(columns))
	for i, col := range columns {
		columnNames[i] = col.Name
	}

	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))

		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, err
		}

		record := make(models.DynamicRecord)
		for i, col := range columns {
			value := values[i]
			if value != nil {
				// 类型转换
				switch col.DataType {
				case "boolean":
					if b, ok := value.(bool); ok {
						record[col.Name] = b
					}
				case "integer", "bigint", "smallint":
					if num, ok := value.(int64); ok {
						record[col.Name] = num
					}
				case "real", "double precision", "numeric", "decimal":
					if num, ok := value.(float64); ok {
						record[col.Name] = num
					}
				default:
					// 字符串和其他类型
					record[col.Name] = value
				}
			} else {
				record[col.Name] = nil
			}
		}

		records = append(records, record)
	}

	return records, nil
}

// isTextColumn 判断是否为文本列
func isTextColumn(dataType string) bool {
	textTypes := []string{"text", "varchar", "character varying", "character", "char"}
	for _, t := range textTypes {
		if strings.Contains(strings.ToLower(dataType), t) {
			return true
		}
	}
	return false
}

// isAutoIncrement 判断是否为自增列
func isAutoIncrement(col introspect.ColumnInfo) bool {
	return strings.Contains(strings.ToLower(col.DefaultValue), "nextval")
}

// isPrimaryKey 判断是否为主键
func isPrimaryKey(columnName string, primaryKeys []string) bool {
	for _, pk := range primaryKeys {
		if pk == columnName {
			return true
		}
	}
	return false
}
